package com.internship.javawebinternship.repository;

import com.internship.javawebinternship.entity.Notification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


public interface NotificationRepository extends JpaRepository<Notification, String> {
    public int countByStuempnoAndReadedEquals(String no, int read);

    public List<Notification> findByStuempno(String no);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "SELECT notification.*,account.name FROM notification,account WHERE notification.stuEmpNo=?1 AND notification.stuEmpNo=account.stuEmpNo ORDER BY readed DESC,DATE DESC ,important DESC", nativeQuery = true)
    public List<Notification> findByStuempnoOrderByReadedDescDateDescImportantDesc(String no);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "UPDATE covid19.notification SET covid19.notification.readed = 1 WHERE covid19.notification.stuEmpNo = :no", nativeQuery = true)
    public void findByStuempnoSetAllReaded1(String no);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "insert into covid19.notification(stuEmpNo, notifyHeader, notifyContent, date, readed, fromStuEmpNo, important) VALUES (?1,?2,?3,?4,?5,?6,?7)", nativeQuery = true)
    public void sendMessage(String no, String header, String content, String date, int readed, String from, int important);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    public void deleteByStuempnoAndReadedAndDateLessThan(String no, int readed, String date);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    public void deleteByStuempno(String no);

    public int countByFromstuempnoAndDateAndReaded(String from, String date, int readed);

    public List<Notification> findByFromstuempnoAndDateAndReadedOrderByStuempnoAsc(String from, String date, int readed);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "SELECT * FROM notification WHERE fromStuEmpNo=?1 GROUP BY date ORDER BY DATE DESC", nativeQuery = true)
    public List<Notification> findByFromstuempnoOrderByDateDescDistinct(String no);

    public int countByFromstuempnoAndDate(String no,String date);
}
